/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.ekuma.epos.datalogic.define.dao;

import com.openbravo.data.loader.TableDBSentenceBuilder;
import e.odbo.DB;
import e.odbo.data.dao.ModifiedLogDAO;
import cn.ekuma.epos.db.table.I_Customer;

import com.openbravo.data.basic.BasicException;
import com.openbravo.data.loader.serialize.DataRead;
import com.openbravo.data.loader.serialize.DataWrite;
import com.openbravo.data.loader.serialize.Datas;
import com.openbravo.data.loader.I_Session;
import com.openbravo.data.loader.sentence.PreparedSentence;
import com.openbravo.data.loader.sqlbuilder.QBFBuilder;
import com.openbravo.data.loader.serialize.serializer.write.SerializerWriteBasic;
import com.openbravo.data.loader.serialize.serializer.write.SerializerWriteParams;
import com.openbravo.data.loader.serialize.serializer.write.SerializerWriteString;
import com.openbravo.data.loader.sentence.StaticSentence;
import com.openbravo.data.loader.table.TableDefinition;
import com.openbravo.data.model.Field;
import com.openbravo.format.Formats;
import com.openbravo.pos.bean.CustomerCategoryExt;
import com.openbravo.bean.BusinessPartner;
import com.openbravo.bean.CustomerCategory;
import com.openbravo.data.loader.serialize.DataParams;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Administrator
 */
public class CustomerDAO extends ModifiedLogDAO<BusinessPartner>{

    public CustomerDAO(I_Session s) {
        super(s);
    }

    
    @Override
    public TableDefinition getTable(){
        return  new TableDefinition(
                "CUSTOMERS"
            ,new Field[]{
                new Field(I_Customer.ID,        Datas.STRING,Formats.STRING,"ID"),
                new Field(I_Customer.TAXID,     Datas.STRING, Formats.STRING),
                new Field(I_Customer.SEARCHKEY, Datas.STRING,Formats.STRING),
                new Field(I_Customer.NAME1,      Datas.STRING,Formats.STRING),
                new Field(I_Customer.CARD,      Datas.STRING,Formats.STRING),
                
                new Field(I_Customer.VISIBLE,   Datas.BOOLEAN, Formats.BOOLEAN, "VISIBLE"),
                new Field(I_Customer.NOTES,     Datas.STRING,Formats.STRING),
                new Field(I_Customer.MAXDEBT,   Datas.DOUBLE, Formats.CURRENCY),
                new Field(I_Customer.CURDATE,   Datas.TIMESTAMP,Formats.TIMESTAMP,true),
                new Field(I_Customer.CURDEBT,   Datas.DOUBLE,Formats.CURRENCY,true),
                
                new Field(I_Customer.FIRSTNAME, Datas.STRING, Formats.STRING),
                new Field(I_Customer.LASTNAME,  Datas.STRING,Formats.STRING),
                new Field(I_Customer.EMAIL,     Datas.STRING, Formats.STRING),
                new Field(I_Customer.PHONE,     Datas.STRING,Formats.STRING),
                new Field(I_Customer.PHONE2,    Datas.STRING,Formats.STRING),
                
                new Field(I_Customer.FAX,       Datas.STRING, Formats.STRING),
                new Field(I_Customer.ADDRESS,   Datas.STRING,Formats.STRING),
                new Field(I_Customer.ADDRESS2,  Datas.STRING,Formats.STRING),
                new Field(I_Customer.POSTAL,    Datas.STRING,Formats.STRING),
                new Field(I_Customer.CITY,      Datas.STRING,Formats.STRING),
                
                new Field(I_Customer.REGION,    Datas.STRING,Formats.STRING),
                new Field(I_Customer.COUNTRY,   Datas.STRING,Formats.STRING),
                new Field(I_Customer.TAXCATEGORY,Datas.STRING,Formats.STRING),
                new Field(I_Customer.CATEGORY,  Datas.STRING,Formats.STRING,CustomerCategory.class),
                new Field(I_Customer.APPPASSWORD,Datas.STRING,Formats.STRING),
                
                new Field(I_Customer.LASTMODIFIED,Datas.TIMESTAMP, Formats.TIMESTAMP),
                new Field(I_Customer.CREATEDATE,Datas.TIMESTAMP, Formats.TIMESTAMP),
                new Field(I_Customer.OPENCREDIT,Datas.INT, Formats.INT),
                new Field(I_Customer.SCORE,Datas.DOUBLE, Formats.DOUBLE,true)
            }
            , new int[] {0}
        );
    }

    @Override
    public void writeInsertValues(DataWrite dp, BusinessPartner obj) throws BasicException {
          dp.setString(1, obj.getId());
          dp.setString(2, obj.getTaxid());
          dp.setString(3, obj.getSearchkey());
          dp.setString(4, obj.getName());
          dp.setString(5, obj.getCard());
          
          dp.setBoolean(6, obj.isVisible());
          dp.setString(7, obj.getNotes());
          dp.setDouble(8, obj.getMaxdebt());
          dp.setTimestamp(9, obj.getCurdate());
          dp.setDouble(10, obj.getCurdebt());  
          
          dp.setString(11, obj.getFirstname());
          dp.setString(12, obj.getLastname());
          dp.setString(13, obj.getEmail());
          dp.setString(14, obj.getPhone());      
          dp.setString(15, obj.getPhone2());
          
          dp.setString(16, obj.getFax());
          dp.setString(17, obj.getAddress());
          dp.setString(18, obj.getAddress2());
          dp.setString(19, obj.getPostal());        
          dp.setString(20, obj.getCity());
          
          dp.setString(21, obj.getRegion());
          dp.setString(22, obj.getCountry());
          dp.setString(23, obj.getTaxCustCategoryID());
          dp.setString(24, obj.getCategoryid());
          dp.setString(25,obj.getPassword());
          
          dp.setTimestamp(26,obj.getLastModified());
          dp.setTimestamp(27,obj.getCreateDate());
          dp.setInt(28, obj.getOpenCredit());
          dp.setDouble(29,obj.getScore());
    }

    
    
    @Override
	public void writeUpdateRealValues(DataWrite dp, BusinessPartner obj)
			throws BasicException {
    	 dp.setString(1, obj.getId());
         dp.setString(2, obj.getTaxid());
         dp.setString(3, obj.getSearchkey());
         dp.setString(4, obj.getName());
         dp.setString(5, obj.getCard());
         
         dp.setBoolean(6, obj.isVisible());
         dp.setString(7, obj.getNotes());
         dp.setDouble(8, obj.getMaxdebt());
         //dp.setTimestamp(9, obj.getCurdate());
         //dp.setDouble(10, obj.getCurdebt());  
         
         dp.setString(9, obj.getFirstname());
         dp.setString(10, obj.getLastname());
         dp.setString(11, obj.getEmail());
         dp.setString(12, obj.getPhone());      
         dp.setString(13, obj.getPhone2());
         
         dp.setString(14, obj.getFax());
         dp.setString(15, obj.getAddress());
         dp.setString(16, obj.getAddress2());
         dp.setString(17, obj.getPostal());        
         dp.setString(18, obj.getCity());
         
         dp.setString(19, obj.getRegion());
         dp.setString(20, obj.getCountry());
         dp.setString(21, obj.getTaxCustCategoryID());
         dp.setString(22, obj.getCategoryid());
         dp.setString(23,obj.getPassword());
         
         dp.setTimestamp(24,obj.getLastModified());
         dp.setTimestamp(25,obj.getCreateDate());
         dp.setInt(26, obj.getOpenCredit());
         //dp.setDouble(29,obj.getScore());
	}


	public BusinessPartner readValues(DataRead dr,BusinessPartner c) throws BasicException {
           if(c==null)
        	   c = new BusinessPartner();
            c.setKey(dr.getString(1));
            c.setTaxid(dr.getString(2));
            c.setSearchkey(dr.getString(3));
            c.setName(dr.getString(4));
            c.setCard(dr.getString(5));

            c.setVisible(dr.getBoolean(6));
            c.setNotes(dr.getString(7));
            c.setMaxdebt(dr.getDouble(8));
            c.setCurdate(dr.getTimestamp(9));
            c.setCurdebt(dr.getDouble(10));  
            
            c.setFirstname(dr.getString(11));
            c.setLastname(dr.getString(12));
            c.setEmail(dr.getString(13));
            c.setPhone(dr.getString(14));      
            c.setPhone2(dr.getString(15));
            
            c.setFax(dr.getString(16));
            c.setAddress(dr.getString(17));
            c.setAddress2(dr.getString(18));
            c.setPostal(dr.getString(19));        
            c.setCity(dr.getString(20));
            
            c.setRegion(dr.getString(21));
            c.setCountry(dr.getString(22));
            c.setTaxCustCategoryID(dr.getString(23));
            c.setCategoryid(dr.getString(24));
            c.setPassword(dr.getString(25));
            
            c.setLastModified(dr.getTimestamp(26));
            c.setCreateDate(dr.getTimestamp(27));
            c.setOpenCredit(dr.getInt(28));
            c.setScore(dr.getDouble(29));
            return c;
    }

     public void updateCurDebt(final double debt ,final Date date,final String customerId) throws BasicException{
    	 new PreparedSentence(s, "UPDATE CUSTOMERS SET CURDEBT =CURDEBT + ?, CURDATE = ? WHERE ID = ?", SerializerWriteParams.INSTANCE).exec(new DataParams() {
             public void writeValues() throws BasicException {
                 setDouble(1, debt);
                 setTimestamp(2, date);
                 setString(3, customerId);}
             });
     }
     
     public void updateCurScore(final String customerId,final double score ) throws BasicException{
    	 new PreparedSentence(s, "UPDATE CUSTOMERS SET SCORE =SCORE + ? WHERE ID = ?", SerializerWriteParams.INSTANCE).exec(new DataParams() {
             public void writeValues() throws BasicException {
                 setDouble(1, score);
                 setString(2, customerId);}
             });
     }
     
     
    public BusinessPartner findCustomerByCard(String card) throws BasicException { 
        return (BusinessPartner) new PreparedSentence(s, ((TableDBSentenceBuilder)getTableBuilder()).getListSQL(getTable())+" WHERE CARD = ? AND VISIBLE = " + s.getDialect().TRUE(), SerializerWriteString.INSTANCE, this).find(card);
    }

    @Override
    public Class getSuportClass() {
        return BusinessPartner.class;
    }

    public int updateCustomerExt(final BusinessPartner customer) throws BasicException {
        return new PreparedSentence(s
                , "UPDATE CUSTOMERS SET NOTES = ?,LASTMODIFIED=? WHERE ID = ?"
                , SerializerWriteParams.INSTANCE
                ).exec(new DataParams() { public void writeValues() throws BasicException {
                        setString(1, customer.getNotes());
                        setTimestamp(2, new Date());
                        setString(3, customer.getId());
                }});
    }

	public void execChangePassword(String id,String newPassword) throws BasicException {
		new StaticSentence(s
                , "UPDATE CUSTOMERS SET APPPASSWORD = ? WHERE ID = ?"
                ,new SerializerWriteBasic(new Datas[] {Datas.STRING, Datas.STRING})).exec(new String[]{newPassword,id});
	}

	public List<BusinessPartner> list(Object filter) throws BasicException {
		
		return new StaticSentence(s, new QBFBuilder(
                ((TableDBSentenceBuilder)getTableBuilder()).getListSQL(getTable())+" WHERE ?(QBF_FILTER) "
                + "ORDER BY TAXID", new String[]{I_Customer.CREATEDATE, I_Customer.CREATEDATE, I_Customer.CATEGORY}), new SerializerWriteBasic(new Datas[]{Datas.OBJECT, Datas.TIMESTAMP, Datas.OBJECT, Datas.TIMESTAMP, Datas.OBJECT, Datas.STRING}), this).list(filter);
	}

	@Override
	public Class transClass(Class in) {
		if(in==CustomerCategoryExt.class)
			return CustomerCategory.class;
		return super.transClass(in);
	}
    
	public List<BusinessPartner> listCustomerByOperationCategory(String id) throws BasicException {
		return new StaticSentence(s, DB.getListSQL(this,"C")+ ",OPERATION_CUSTOMER OC,OPERATION_CUSTOMER_CATEGORIES OCC WHERE C.ID=OC.ID  AND OC.OPERATIONCATEGORIE=OCC.ID AND OCC.ID=?", SerializerWriteString.INSTANCE, this).list(id);
	}

	public BusinessPartner findCustomerByStoreCard(String cardNum) throws BasicException {
		return (BusinessPartner) new PreparedSentence(s, ((TableDBSentenceBuilder)getTableBuilder()).getListSQL(getTable())+" WHERE ID = (SELECT CUSTOMER_ID FROM STOREVALUECARD WHERE CARDNUM = ? AND STATE = 1 ) AND VISIBLE = " + s.getDialect().TRUE(), SerializerWriteString.INSTANCE, this).find(cardNum);
	}
	
}
